import pymysql
from pymysql.connections import Connection
import sqlite3
from warnings import filterwarnings

from Common.ReadYaml import ConfigYaml
from Common.Route import Any_Path

filterwarnings('ignore', category=pymysql.Warning)


class Sql:
    def __init__(self, sql=None, db='Sqlite.db', encoding="utf-8", switch=True, sqlname=None):
        '''

        :param sql:  sql语句
        :param db:  数据库名称
        '''
        if sqlname is None:
            self.sqlname = ConfigYaml("sqlname").base_config
        else:
            self.sqlname = sqlname
        self.sql = sql
        self.db = db
        self.switch = switch
        self.encoding = encoding
        self.mysql = ConfigYaml('mysql').base_config
        self.sqlite_path = Any_Path('Config', self.db)
        self.sqlite = ConfigYaml('sqlite').base_config
        self.host = ConfigYaml('host').base_config
        self.port = int(ConfigYaml('port').base_config)
        self.pwd = ConfigYaml('pwd').base_config
        self.user = ConfigYaml('user').base_config
        self.system_host = ConfigYaml('testsql_host').base_config
        self.system_port = ConfigYaml('testsql_port').base_config
        self.system_user = ConfigYaml('testsql_user').base_config
        self.system_pwd = ConfigYaml('testsql_pwd').base_config
        if self.sqlite:
            try:
                self.conn = sqlite3.connect(self.sqlite_path)
            except:
                raise ConnectionError("sqlite connect error,please check configuration ....")
        else:
            if self.switch:
                try:
                    self.conn = Connection(host=self.system_host, user=self.system_user, password=self.system_pwd, database=self.sqlname,
                                            port=self.system_port, charset='utf8')
                except:
                    raise ConnectionError("mysql connect error,please check configuration ....")

            else:
                try:
                    self.conn = Connection(host=self.host, user=self.user, password=self.pwd, database=self.sqlname,
                                           port=self.port, charset='utf8')
                except:
                    raise ConnectionError("mysql connect error,please check configuration ....")

    def execute_sql(self):
        '''
        执行sql语句
        :return:
        '''
        if self.switch:
            cursor = self.conn.cursor(pymysql.cursors.DictCursor)
        else:
            cursor = self.conn.cursor()
        cursor.execute(self.sql)
        values = cursor.fetchall()
        self.conn.commit()
        cursor.close()
        self.conn.close()
        return values



if __name__=="__main__":
    create_response = "CREATE TABLE IF NOT EXISTS response(id INTEGER primary key NOT NULL AUTO_INCREMENT,funname varchar (100),describes varchar(1000),priority varchar(50),address varchar(1000),result varchar(30), writer varchar(30),usetime float ,summary text(20000000));"
    create_result = "CREATE TABLE IF NOT EXISTS result(id INTEGER primary key NOT NULL AUTO_INCREMENT,funname varchar (100),totalnumber int default 0,passnumber int default 0, errornumber int default 0,failnumber int default 0,passrate float default 0,errorrate float default 0,failrate float default 0,modular varchar (30));"
    create_status = "CREATE TABLE IF NOT EXISTS `case_status`(`status_id` INT NOT NULL, version varchar(100),science varchar(100), total_case varchar(100), duration varchar(100), start_time varchar(100), end_time varchar(100), max_time varchar(100), avg_time varchar(100), success_case varchar(100), fail_case varchar(100), report_title varchar(500), PRIMARY KEY ( `status_id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;"
    create_history = "CREATE TABLE IF NOT EXISTS case_history(id INTEGER primary key NOT NULL AUTO_INCREMENT,funname varchar (100),describes varchar(1000),priority varchar(50),address varchar(1000),result varchar(30), writer varchar(30),usetime float ,summary text(20000000), status INT NOT NULL , FOREIGN KEY (status) REFERENCES case_status(status_id) on delete cascade on update cascade);"
    insert_startus = "INSERT INTO case_status(status_id,version,science,total_case,duration,start_time,end_time,max_time,avg_time,success_case,fail_case,report_title) VALUES (1,'V.1.0.0','103环境','162条','386.59秒','2020-12-03:10:15:44','2020-12-03:10:22:10','0.6821 (秒)','0.0657 (秒)','152 (条)','9 (条)','103(环境)ERP接口自动化测试报告');"


    sql_sentence = "SELECT id, package_number FROM package WHERE is_confirmed_sku_quantity=0 and warehouse_id=%s and package_number is not null and status=2;" % (
        8)
    data_info = Sql(sql=create_history, switch=False).execute_sql()
    print(data_info)







